package co.th.bjc.md.helper.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;

import co.th.bjc.md.dao.Department;
import co.th.bjc.md.helper.entries.HelperDatabse;

public class DepartmentHelper extends HelperDatabse{
	
	private Logger _log = Logger.getLogger(getClass().getName());
	
	public Department findByDepartmentCode(Integer departmentCode) throws Exception{
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Department department = null;
		try {
			con = getConnection();
			stmt = con.prepareStatement(DEPARTMENT.FIND_BY_DEPARTMENT_CODE);
			stmt.setInt(1, departmentCode); 
			
			rs = stmt.executeQuery();
			if (rs.next()){
				department = setDepartment(rs);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (con != null) con.close();
		}
		return department;
	}
	
	public List<Department> findByAll() throws Exception{
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Department> listDepartment = new ArrayList<Department>(0);
		try {
			con = getConnection();
			stmt = con.prepareStatement(DEPARTMENT.FIND_ALL);
			
			rs = stmt.executeQuery();
			while (rs.next()){
				Department department = setDepartment(rs);
				listDepartment.add(department);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (con != null) con.close();
		}
		return listDepartment;
	}
	
	public Department addDepartment(Connection conn, Department input) throws Exception {
		boolean isConnNull = false;
		try {
			if (conn==null){
				conn = getConnection();
				conn.setAutoCommit(true);
				isConnNull = true;
			}

			input = insert(conn, input);
		} catch (Exception ex) {
			ex.printStackTrace();
		}finally{
			if (conn != null && isConnNull){
				conn.close();
			}
		}
		return input;
	}
	
	public List<Department> addDepartment(Connection conn, List<Department> input) throws Exception {
		boolean isConnNull = false;
		try {
			if (conn==null){
				conn = getConnection();
				isConnNull = true;
				conn.setAutoCommit(true);
			}
			
			for(Department department : input){
				if(department.getDepartmentCode() != null){
					update(conn, department);
				}else {
					department = insert(conn, department);
				}
				
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}finally{
			if (conn != null && isConnNull){
				conn.close();
			}
		}
		return input;
	}
	
	public boolean update(Connection conn, List<Department> input) throws Exception {
		boolean isConnNull = false;
		try {
			if (conn==null){
				conn = getConnection();
				isConnNull = true;
				conn.setAutoCommit(true);
			}

			for(Department department : input){
				if (!edit(conn, department)) return false;
			}
			return true;
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		}finally{
			if (conn != null && isConnNull){
				conn.close();
			}
		}
	}
	
	public boolean update(Connection conn, Department input) throws Exception {
		boolean isConnNull = false;
		try {
			if (conn==null){
				conn = getConnection();
				conn.setAutoCommit(true);
				isConnNull = true;
			}

			return edit(conn, input);
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		}finally{
			if (conn != null && isConnNull){
				conn.close();
			}
		}
	}
	
	public boolean delete(Connection conn, Integer departmentCode) throws SQLException{
		boolean isConnNull = false;
		try {	
			if (conn==null){
				conn = getConnection();
				isConnNull = true;
				conn.setAutoCommit(true);
			}
			
			Department department = new Department();
			department.setDepartmentCode(departmentCode);
			return delete(conn, department);
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		}finally{
			if (conn != null && isConnNull){
				conn.close();
			}
		}
	}

	/* ================================================================================================= */
	
	private synchronized Integer getSequence(Connection conn) throws SQLException{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Integer sequence = null;
		try {
			stmt = conn.prepareStatement(PRODUCT.SEQUENCE);
			rs = stmt.executeQuery();
			if (rs.next()){
				sequence = Integer.valueOf((rs.getInt(1) + 1));
			}
		} catch (Exception ex) {
			_log.error(getClass() + " Get Sequence : " + ex.getMessage());
			ex.printStackTrace();
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
		}
		return sequence;
	}
	
	private synchronized Department insert(Connection conn, Department input) throws Exception {
		PreparedStatement stmt = null;
		try {			
			input.setDepartmentCode(getSequence(conn));
			
			stmt = conn.prepareStatement(DEPARTMENT.INSERT);
			stmt.setInt(1, input.getDepartmentCode());
			stmt.setString(2, input.getDepartmentName());
			stmt.setString(3, input.getDepartmentDetail());
			stmt.setString(4, input.getCreateBy());
			stmt.setDate(5, new java.sql.Date(System.currentTimeMillis()));
			stmt.setString(6, input.getUpdateBy());
			stmt.setDate(7, new java.sql.Date(System.currentTimeMillis()));

			stmt.executeUpdate();
			
		} catch (Exception ex) {
			_log.error("Insert Fail");
			ex.printStackTrace();
		} finally {
			if (stmt != null)
				stmt.close();
		}
		return input;
	}
	
	private synchronized boolean edit(Connection conn, Department input) throws Exception {
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(DEPARTMENT.UPDATE);
			
			stmt.setString(1, input.getDepartmentName());
			stmt.setString(2, input.getDepartmentDetail());
			stmt.setString(3, input.getUpdateBy());
			stmt.setDate(4, new java.sql.Date(System.currentTimeMillis()));
			
			stmt.setInt(5, input.getDepartmentCode());

			int record = stmt.executeUpdate();
			return record > 0 ? true : false;
		} catch (Exception ex) {
			_log.error("Update Fail : " + input.getDepartmentCode());
			ex.printStackTrace();
			return false;
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}
	
	private synchronized boolean delete(Connection conn, Department input) throws SQLException{
		PreparedStatement stmt = null;
		try {	
			stmt = conn.prepareStatement(DEPARTMENT.DELETE);
			stmt.setInt(1, input.getDepartmentCode().intValue());

			int record = stmt.executeUpdate();
			return record > 0 ? true : false;
		} catch (Exception ex) {
			_log.error("Delete Fail : " + input.getDepartmentCode());
			ex.printStackTrace();
			return false;
		}
	}
	
	private Department setDepartment(ResultSet rs) throws Exception{
		Department department = new Department();
		department.setDepartmentCode(rs.getInt("department_code"));
		department.setDepartmentName(rs.getString("department_name"));
		department.setDepartmentDetail(rs.getString("department_detail"));
		department.setCreateBy(rs.getString("create_by"));
		department.setCreateDate(new Date(rs.getTimestamp("create_date").getTime()));
		department.setUpdateBy(rs.getString("update_by"));
		department.setUpdateDate(new Date(rs.getTimestamp("update_date").getTime()));
		return department;
	}
}
